﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;

namespace CommonUtils.Database
{
    namespace CreditILService
    {
        /// <summary>
        /// Database related utils
        /// </summary>
        public class DatabaseAdapter<ConnectionType> where ConnectionType: IDbConnection, new()
        {
            #region Constructors

            /// <summary>
            /// Constructor by connection string
            /// </summary>
            /// <param name="connectionString">Connection string</param>
            public DatabaseAdapter(string connectionString)
            {
                connectionStringBuilder = new DbConnectionStringBuilder {ConnectionString = connectionString};
            }

            /// <summary>
            /// Constructor by connection string builder
            /// </summary>
            /// <param name="connectionStringBuilder"></param>
            public DatabaseAdapter(DbConnectionStringBuilder connectionStringBuilder)
            {
                this.connectionStringBuilder = connectionStringBuilder;
            }

            #endregion

            #region Properties

            /// <summary>
            /// Connection string builder
            /// </summary>
            protected readonly DbConnectionStringBuilder connectionStringBuilder;

            /// <summary>
            /// Connection string builder
            /// </summary>
            public DbConnectionStringBuilder ConnectionStringBuilder
            {
                [DebuggerStepThrough]
                get
                {
                    return connectionStringBuilder;
                }
            }

            #endregion

            #region Implementation

            /// <summary>
            /// Default processor for data reader
            /// </summary>
            /// <param name="reader">Data reader</param>
            /// <returns>Result as list of arrays of objects</returns>
            private List<object[]> DefaultDataReaderProcessor(IDataReader reader)
            {
                List<object[]> retValue = new List<object[]>();
                while (reader.Read())
                {
                    object[] values = new object[reader.FieldCount];
                    reader.GetValues(values);
                    retValue.Add(values);
                }
                return retValue;
            }

            /// <summary>
            /// Processor for data reader for queries with the only row as result
            /// </summary>
            /// <param name="reader">Data reader</param>
            /// <returns>Result as array of objects</returns>
            private object[] SingleRowDataReaderProcessor(IDataReader reader)
            {
                List<object[]> readerResult = DefaultDataReaderProcessor(reader);
                if (readerResult.Count != 1)
                {
                    throw new Exception(string.Format("Query returns {0} rows while the only row expected as result",
                                                      readerResult.Count));
                }
                return readerResult[0];
            }

            /// <summary>
            /// Do nothing with query result
            /// </summary>
            /// <param name="reader"></param>
            private object EmptyReaderProcessor(IDataReader reader)
            {
                return new object();
            }

            /// <summary>
            /// Delegate to process IDataReader information
            /// </summary>
            /// <typeparam name="ResultType">Type of result to return</typeparam>
            /// <param name="reader">Any class that implements IDataReader interface</param>
            /// <returns>Result</returns>
            private delegate ResultType DataReaderProcessor<ResultType>(IDataReader reader);

            /// <summary>
            /// Execute query on database
            /// </summary>
            /// <param name="query">Query to execute</param>
            /// <param name="readerProcessor">DataReader processor</param>
            /// <returns>DataReader processor result</returns>
            private ResultType ExecuteQuery<ResultType>(string query, DataReaderProcessor<ResultType> readerProcessor)
            {
                using (DbConnection connection = new SqlConnection(ConnectionStringBuilder.ConnectionString))
                    //ConnectionType(ConnectionStringBuilder.ConnectionString))
                {
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = query;
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        return readerProcessor(command.ExecuteReader());
                    }
                }
            }


            /// <summary>
            /// Execute query on database
            /// </summary>
            /// <param name="query">Query to execute</param>
            /// <returns>Result as list of arrays of objects</returns>
            public List<object[]> ExecuteQuery(string query)
            {
                return ExecuteQuery<List<object[]>>(query, DefaultDataReaderProcessor);
            }

            /// <summary>
            /// Execute query with the only row as result
            /// </summary>
            /// <param name="query">Query to execute</param>
            /// <returns>Result as array of objects</returns>
            public object[] ExecuteSingleRowResultQuery(string query)
            {
                return ExecuteQuery<object[]>(query, SingleRowDataReaderProcessor);
            }

            #endregion
        }
    }
}